// Copyright (C) 2025, ZWSOFT Co., LTD.(Guangzhou)
// Licensed under the MIT License. See LICENSE in the project root for license information.

using Microsoft.Data.Sqlite;
using System.Text;
using TestClient.ViewModel;

namespace TestClient.Domain
{
    class CompositeDAO(SqliteConnection connection)
    {
        static readonly string laminateUpsertSql = MakeUpsertQuery("FibLaminate", "Name", ["LayupSurface"]);
        static readonly string plyInsertSql = MakeInsertQuery("FibPly",
                ["Name", "Parent", "Material", "Sequence", "Step", "SpecifiedOrientation", "NetBoundary", "ExtBoundary"]);
        static readonly string plyUpdateSql = MakeUpdateQuery("FibPly", "Name",
                ["Parent", "Material", "Sequence", "Step", "SpecifiedOrientation", "NetBoundary", "ExtBoundary"]);

        public void InsertOrUpdateLaminate(string name, string surfaceId, SqliteTransaction transaction)
        {
            var command = connection.CreateCommand();
            command.CommandText = laminateUpsertSql;
            command.Transaction = transaction;
            command.Parameters.AddWithValue("@Name", name);
            command.Parameters.AddWithValue("@LayupSurface", surfaceId);
            command.ExecuteNonQuery();
        }

        public void InsertOrUpdatePly(PlyDataInput input, SqliteTransaction transaction)
        {
            var command = connection.CreateCommand();
            command.Transaction = transaction;
            UpdateInsertPlyData(input, command);
        }

        public void InsertOrUpdatePlies(IEnumerable<PlyDataInput> plies, SqliteTransaction transaction)
        {
            var command = connection.CreateCommand();
            command.Transaction = transaction;
            foreach (var ply in plies)
            {
                command.Parameters.Clear();
                UpdateInsertPlyData(ply, command);
            }
        }

        private static void UpdateInsertPlyData(PlyDataInput input, SqliteCommand command)
        {
            // update; if update count is 0, insert
            command.CommandText = plyUpdateSql;
            command.Parameters.AddWithValue("@Name", input.Name);
            command.Parameters.AddWithValue("@Parent", input.Parent);
            command.Parameters.AddWithValue("@Material", input.MaterialName);
            command.Parameters.AddWithValue("@Sequence", input.Sequence);
            command.Parameters.AddWithValue("@Step", input.Step);
            command.Parameters.AddWithValue("@SpecifiedOrientation", input.Orientation);
            command.Parameters.AddWithValue("@NetBoundary", input.NetBoundaryId);
            command.Parameters.AddWithValue("@ExtBoundary", input.ExtendBoundaryId);
            var changed = command.ExecuteNonQuery();
            if (changed == 0)
            {
                command.CommandText = plyInsertSql;
                command.ExecuteNonQuery();
            }
        }

        private static string MakeUpdateQuery(string tableName, string primaryKey, string[] columnNames)
        {
            // "Update {tableName} set {columnNames=@columnNames} where {primaryKey=@primaryKey}"
            string updateList = string.Join(", ", columnNames.Select(c => $"{c}=@{c}"));

            return $"Update {tableName} set {updateList} where {primaryKey}=@{primaryKey}";
        }

        private static string MakeInsertQuery(string tableName, string[] columnNames)
        {
            // "Insert into {tableName} ({columnNames}) values ({@columnNames})"
            string columns = string.Join(", ", columnNames);
            string updateList = string.Join(", ", columnNames.Select(c => $"@{c}"));

            return $"Insert into {tableName} ({columns}) values ({updateList})";
        }

        private static string MakeUpsertQuery(string tableName, string primaryKey, string[] columnNames)
        {
            string[] allColumns = new[] { primaryKey }.Concat(columnNames).ToArray();

            // Create the comma-separated list of column names for the INSERT INTO clause.
            // Example: "primaryKey, key1, key2"
            string columnList = string.Join(", ", allColumns);

            // Create the comma-separated list of parameter placeholders for the VALUES clause.
            // Example: "@primaryKey, @key1, @key2"
            string valueList = string.Join(", ", allColumns.Select(c => "@" + c));

            // Create the comma-separated list of assignment expressions for the DO UPDATE SET clause.
            // This will not include the primary key.
            // Example: "key1=@key1, key2=@key2"
            string updateList = string.Join(", ", columnNames.Select(c => $"{c}=@{c}"));

            // Construct the final UPSERT query string.
            var queryBuilder = new StringBuilder();
            queryBuilder.Append($"INSERT INTO {tableName}({columnList}) ");
            queryBuilder.Append($"VALUES({valueList}) ");
            queryBuilder.Append($"ON CONFLICT({primaryKey}) DO UPDATE SET {updateList};");

            return queryBuilder.ToString();
        }
    }
}
